-define(CLEAR_DB,
  "DELETE  FROM [DeviceTesting].[dbo].[tests];
DELETE  FROM [DeviceTesting].[dbo].[answers];
DELETE  FROM [DeviceTesting].[dbo].[messages];
DELETE  FROM [DeviceTesting].[dbo].[states];").

-define(GET_ALL_IDS, "SELECT [testId] FROM [DeviceTesting].[dbo].[tests]").

-define(GET_TEST_BY_ID, "SELECT [testId] ,[deviceId] ,[descr] FROM [DeviceTesting].[dbo].[tests] WHERE [testId] = ?").

-define(GET_TEST_OUTPUT, "SELECT [id] ,[fh] ,[deviceCode] ,[operation] ,[data] ,[fk] ,[crc] ,[testId] ,[orderNumber]
,[out]  FROM [DeviceTesting].[dbo].[messages]  WHERE testId = ? AND [out] = 1  ORDER BY orderNumber DESC").

-define(GET_TEST_INPUT, "SELECT [id] ,[fh] ,[deviceCode] ,[operation] ,[data] ,[fk] ,[crc] ,[testId] ,[orderNumber]
,[out]  FROM [DeviceTesting].[dbo].[messages]  WHERE testId = ? AND [out] = 0  ORDER BY orderNumber DESC").

-define(ADD_TEST, "INSERT INTO [DeviceTesting].[dbo].[tests] (testId, deviceId, descr) VALUES (?, ?, ?)").

-define(ADD_MESSAGE, "
INSERT INTO [DeviceTesting].[dbo].[messages]
           ([id]
           ,[fh]
           ,[deviceCode]
           ,[operation]
           ,[data]
           ,[fk]
           ,[crc]
           ,[testId]
           ,[orderNumber]
           ,[out])
     VALUES
           (?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?)").

-define(GET_STATE_BY_ID, "SELECT [id]
      ,[testId]
      ,[deviceId]
      ,[completed]
      ,[sends]
      ,[fails]
      ,[startStamp]
      ,[endStamp]
  FROM [DeviceTesting].[dbo].[states]").

-define(UPDATE_STATE, "UPDATE [DeviceTesting].[dbo].[states]
   SET [completed] = ?
      ,[sends] = ?
      ,[fails] = ?
 WHERE [id] = ?").

-define(UPDATE_STATE_WITH_DATE, "UPDATE [DeviceTesting].[dbo].[states]
   SET [completed] = ?
      ,[sends] = ?
      ,[fails] = ?
      ,[endStamp] = GETDATE()
 WHERE [id] = ?").

-define(CREATE_STATE, "
INSERT INTO [DeviceTesting].[dbo].[states]
           ([id]
           ,[testId]
           ,[deviceId]
           ,[completed]
           ,[sends]
           ,[fails]
           ,[startStamp]
           ,[endStamp])
     VALUES
           (?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,GETDATE()
           ,NULL)
").

-define(GET_SENDS, "SELECT ISNULL((SELECT TOP 1 [sends]
  FROM [DeviceTesting].[dbo].[states] WHERE [id] = ?), -1)").

-define(ADD_ANSWERS, "INSERT INTO [DeviceTesting].[dbo].[answers]
           ([stateId]
           ,[id]
           ,[fh]
           ,[deviceCode]
           ,[operation]
           ,[data]
           ,[fk]
           ,[crc]
           ,[orderNumber])
     VALUES
           (?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?)").

-define(GET_LAST_STATE_BY_DEVICE, "SELECT TOP 1
       [id]
      ,[testId]
      ,[deviceId]
      ,[completed]
      ,[sends]
      ,[fails]
      ,[startStamp]
      ,[endStamp]
  FROM [DeviceTesting].[dbo].[states] WHERE [deviceId] = ?
  ORDER BY [startStamp] DESC").

-define(GET_ANSWERS_BY_STATE, "SELECT [stateId]
      ,[id]
      ,[fh]
      ,[deviceCode]
      ,[operation]
      ,[data]
      ,[fk]
      ,[crc]
      ,[orderNumber]
  FROM [DeviceTesting].[dbo].[answers] WHERE [stateId] = ?
  ORDER BY orderNumber DESC").